/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.q1cc.cfs.jamp;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import net.q1cc.cfs.jamp.event.EventType;
import net.q1cc.cfs.jamp.event.JAmpAbstractEvent;
import net.q1cc.cfs.jamp.event.JAmpEvent;
import net.q1cc.cfs.jamp.event.JAmpEventListener;
import net.q1cc.cfs.jamp.event.JAmpSongEvent;
import net.q1cc.cfs.jamp.schema.Song;

/**
 *
 * @author claus
 */
public class DBHandler extends AbstractTableModel {

    Connection con;
    String server, user, passwd, db, table_prefix;
    ArrayList<String> selectedRows;
    ArrayList<Song> currentPlaylist;
    int resultSize;
    int currentSongRow = -1;
    TableRowSorter<TableModel> sorter;
    boolean shuffle = false;
    Statement stmt;

    public DBHandler(String server, String user, String passwd, String db, String table_prefix) throws SQLException, ClassNotFoundException {
        this.server = server;
        this.user = user;
        this.passwd = passwd;
        this.db = db;
        this.table_prefix = table_prefix;
        //Class.forName("com.mysql.jdbc.Driver");
        //String url = "jdbc:mysql://"+server+"/"+db;
        //con = DriverManager.getConnection(url, user, passwd);
        Class.forName("org.sqlite.JDBC");
        con = DriverManager.getConnection("jdbc:sqlite:" + Main.getInstance().settings.getProperty("db.folder") + "jampdb.sqlite");
        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        sorter = new TableRowSorter<TableModel>();
        initTable();
        Main.getInstance().eventReporter.addListener(new JAmpEventListener() {

            public void handleEvent(JAmpEvent event) {
                switch(event.getType()){
                    case SONG:
                        switch(event.getEventID()){
                            case JAmpSongEvent.SONG_ENDED:
                                nextSong();
                                break;
                            default:
                                break;
                        }
                        break;
                    default:
                        break;
                }
            }
        }, EventType.GENERAL);
        
    }

    public void setComparators() {
        Comparator myC = new MyComparator(true);
        sorter.setComparator(Song.getKey("Rating"), myC);
        sorter.setComparator(Song.getKey("Play Count"), myC);
        //sorter.setComparator(Song.getKey("Track ID"),myC);
        sorter.setComparator(Song.getKey("Total Time"), myC);
    }

    public ResultSet query(String statement) throws SQLException {

        ResultSet result = stmt.executeQuery(statement);

        return result;
    }

    public int update(String statement) throws SQLException {
        int result = stmt.executeUpdate(statement);
        return result;
    }

    public boolean usePlaylist(String playListName) {
        //TODO playList einlesen und in das TableModel fügen
        //tabelle benachrichtigen
        return true;
    }

    public boolean search(String s, boolean inName, boolean inArtist, boolean inAlbum, boolean inGenre, boolean inComments) {
        s=s.trim();
        if(s.equals("")) {
            useQuery("");
            return true;
        }
        StringBuilder query = new StringBuilder(
                "SELECT * FROM `");
        query.append(table_prefix);
        query.append("tracks` WHERE ( ");

        s = s.replaceAll("'", "\\'");

        boolean first = true;
        if (inName) {
            if (first) {
                first = false;
            } else {
                query.append("OR");
            }
            query.append("(`Name` LIKE '%");
            query.append(s);
            query.append("%' )");
        }
        //<editor-fold defaultstate="collapsed" desc="searching in...">
        if (inArtist) {
            if (first) {
                first = false;
            } else {
                query.append(" OR ");
            }
            query.append("(`Artist` LIKE '%");
            query.append(s);
            query.append("%' )");
        }
        if (inAlbum) {
            if (first) {
                first = false;
            } else {
                query.append(" OR ");
            }
            query.append("(`Album` LIKE '%");
            query.append(s);
            query.append("%' )");
        }
        if (inGenre) {
            if (first) {
                first = false;
            } else {
                query.append(" OR ");
            }
            query.append("(`Genre` LIKE '%");
            query.append(s);
            query.append(".*' )");
        }
        if (inComments) {
            if (first) {
                first = false;
            } else {
                query.append(" OR ");
            }
            query.append("(`Comments` LIKE '%");
            query.append(s);
            query.append("%' )");
        }
        //</editor-fold>

        query.append(" )>0;");
        System.out.println(query.toString());
        return useQuery(query.toString());
    }

    public boolean useQuery(String query) {


        try {
            if (query != null) {
                if (query.matches("SORT") || query.matches("sort")) {
                    //TODO regex anpassen
                } else {
                    if (query.charAt(query.length() - 1) == ';') {
                        query = query.substring(0, query.length() - 1);
                    } else if (query.charAt(query.length() - 2) == ';') {
                        query = query.substring(0, query.length() - 2);
                    }
                    query = query + " ORDER BY `Artist` ASC,`Name` ASC";

                }
                ResultSet s = query(query);
                readResultSet(s);
                s.close();

            } else {
                query =
                        //"SELECT `Name`,`Artist`,`Album`,`Genre`,`Rating`,`Play Count`,`Total Time`, `Track ID`, `Location` FROM `"+table_prefix+"tracks` WHERE `Artist`='Binärpilot';";
                        "SELECT `Name`,`Artist`,`Album`,`Genre`,`Rating`,`Play Count`,`Total Time`, `Track ID`, `Location` FROM `" + table_prefix + "tracks` "
                        + "WHERE NOT `Movie` AND NOT `TV Show` AND ( ( `Genre`!='Hörbuch' AND `Genre`!='Books & Spoken' AND `Genre`!='Hörbuch & Gesprochene Inhalte' ) OR `Genre` is NULL )"
                        + "ORDER BY `Artist` ASC,`Album` ASC,`Name` ASC;";
                ResultSet s = query(query);
                readResultSet(s);
                s.close();
                ArrayList<String> tables = new ArrayList<String>();
                /*for(;currentResultSet.next();)
                {
                System.out.println(currentResultSet.getString("Name"));
                
                }*/



            }


        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        fireTableDataChanged();
        return true;

    }

    /*public static String[] keys= { "Persistent ID", "Album Rating Computed", "Kind", "Year", "File Folder Count",
    "Genre", "Date Modified", "Album Rating", "Artist", "Play Date", "Album", "Size", "Play Date UTC",
    "Comments", "Sample Rate", "Location", "Bit Rate", "Date Added", "Name", "Library Folder Count",
    "Track Type", "Total Time", "Rating", "Play Count", "Track Number", "Composer", "Skip Count",
    "Skip Date", "BPM"
    }; */
    private void initTable() {
        selectedRows=new ArrayList<String>();
        String rows=Main.getInstance().settings.getProperty("gui.selectedRows","*");
        if(rows.equals("*")||rows.equals("")){
            for (int i=0;i<Song.keys.size();i++){
                selectedRows.add(Song.keys.get(i));
            }
        } else {
            selectedRows.addAll(Arrays.asList(rows.split("||")));
            
        }
            
        //useQuery(null);

        sorter.setModel(this);
        setComparators();

    }

    @Override
    public int getRowCount() {

        return resultSize;

    }

    @Override
    public int getColumnCount() {
        return selectedRows.size();
    }

    @Override
    public String getColumnName(int columnIndex) {
        return selectedRows.get(columnIndex);
    }
    /*@Override
    public Class<?> getColumnClass(int columnIndex) {
    throw new UnsupportedOperationException("Not supported yet.");
    }*/ //Not needed?

    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return false;
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {

        return currentPlaylist.get(rowIndex).getValue(selectedRows.get(columnIndex));
    }
    
        public Song getSongAt(int rowIndex) {

        return currentPlaylist.get(rowIndex);
    }
        
    @Override
    public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
        //TODO make editing possible
    }

    public void playSong(int row) {

        String loc = getSongAt(row).getValue("Location").toString();
        currentSongRow = row;
        Main.getInstance().getPlayer().playFile(loc);
        Main.getInstance().bigGUI.setCurrentSongRow(row);

    }

    public void nextSong() {
        if (getRowCount() <= currentSongRow) {
            Main.getInstance().bigGUI.listEnded();
            currentSongRow = -1;
            return;
        }
        if (shuffle) {
            playSong(((int) ((Math.random() * getRowCount()) + 1)));
        } else {
            playSong(currentSongRow + 1);
        }
    }

    public void previousSong() {
        if (getRowCount() <= currentSongRow) {
            Main.getInstance().bigGUI.listEnded();
            currentSongRow = -1;
            return;
        }

        if (shuffle) {
            playSong(((int) ((Math.random() * getRowCount()) + 1)));
        } else {
            playSong(currentSongRow - 1);
        }
    }

    private void readResultSet(ResultSet s) {
        try {
            ArrayList<Song> songs = new ArrayList<Song>();
            int numKeys = Song.keys.size();
            int[] keyToColumn = new int[numKeys];
            for (int i = 0; i < numKeys; i++) {
                try {
                    keyToColumn[i] = s.findColumn(Song.keys.get(i));
                } catch (SQLException e) {
                    keyToColumn[i]=-1;
                }
            }

            while (s.next()) {
                String[] keys = new String[numKeys];
                for (int i = 0; i < numKeys; i++) {
                    if(keyToColumn[i] != -1)
                        keys[i] = s.getString(keyToColumn[i]);
                }
                songs.add(new Song(keys));
            }
            currentPlaylist = songs;
            resultSize = songs.size();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    class MyComparator implements Comparator {

        protected boolean isSortAsc;

        public MyComparator(boolean sortAsc) {
            isSortAsc = sortAsc;
        }

        public int compare(Object o1, Object o2) {
            if (!(o1 instanceof Integer) || !(o2 instanceof Integer)) {
                return 0;
            }
            Integer s1 = (Integer) o1;
            Integer s2 = (Integer) o2;
            int result = 0;
            result = s1.compareTo(s2);
            if (!isSortAsc) {
                result = -result;
            }
            return result;
        }

        public boolean equals(Object obj) {
            if (obj instanceof MyComparator) {
                MyComparator compObj = (MyComparator) obj;
                return compObj.isSortAsc == isSortAsc;
            }
            return false;
        }
    }
}
